package cn.kduck;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.ai.tool.annotation.Tool;
import org.springframework.ai.tool.annotation.ToolParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class JdbcToolsService {

    private final Log logger = LogFactory.getLog(getClass());

    @Autowired
    private DataSource dataSource;

    ObjectMapper objectMapper = new ObjectMapper();

    @Tool(description = "查询数据库中的数据表")
    public String listTable() {
        logger.info("listTable==>");
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getTables(null, null, "%", new String[] { "TABLE" });

            List<Map<String, String>> tables = new ArrayList<>();
            while (rs.next()) {
                Map<String, String> table = new HashMap<>();
                table.put("TABLE_CAT", rs.getString("TABLE_CAT"));
                table.put("TABLE_SCHEM", rs.getString("TABLE_SCHEM"));
                table.put("TABLE_NAME", rs.getString("TABLE_NAME"));
                tables.add(table);
            }
            return objectMapper.writeValueAsString(tables);
        } catch (Exception e) {
            throw new RuntimeException("查询表错误: " + e.getMessage(), e);
        }
    }

    @Tool(description = "根据给定的sql语句从数据库中查询数据")
    public String queryBySql(@ToolParam(description="一条SELECT的SQL查询语句")String sql){
        try {
            sql = new String(sql.getBytes(), "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        logger.info("queryBySql==>" + sql);
        try(Connection conn = dataSource.getConnection()){
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            List<Map<String, Object>> resultList = new ArrayList<>();
            while (resultSet.next()){
                Map<String, Object> rowValueMap = new HashMap<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    int columnType = metaData.getColumnType(i);
                    Object value = resultSet.getObject(i);
                    if (value != null) {
                        rowValueMap.put(columnName,value);
                    }else{
                        rowValueMap.put(columnName,null);
                    }

                }
                resultList.add(rowValueMap);
            }

            return "查询成功，共查询出数据" + resultList.size() + "条数据，具体数据转换为JSON为：" + objectMapper.writeValueAsString(resultList);
        } catch (SQLException e) {
            logger.error("查询SQL执行失败",e);
            return "查询SQL执行失败：" + e;
        } catch (JsonProcessingException e) {
            throw new RuntimeException("查询结果转换为JSON错误", e);
        }
    }

//    private String test(String garbledInput){
//        try {
//            byte[] bytes = garbledInput.getBytes();
//
//            CharsetDetector detector = new CharsetDetector();
//            detector.setText(bytes);
//            CharsetMatch match = detector.detect();
//
//            if (match != null) {
//                String detectedCharset = match.getName();
//                logger.info("检测到的编码：" + detectedCharset);
//
//                // 使用检测到的编码重新解码字节数组
//                String correctText = new String(bytes, "UTF-8");
//                logger.info("修复后的文本：" + correctText);
//                return correctText;
//            }
//            logger.info("无法检测到编码！");
//            return garbledInput;
//        } catch (Exception e) {
//            throw new RuntimeException("编码转换错误", e);
//        }
//    }

    @Tool(description = "根据给定的sql语句对表数据进行修改（插入、更新、删除）")
    public String updateBySql(@ToolParam(description="一条INSERT或UPDATE或DELETE的SQL语句")String sql){
        logger.info("updateBySql==>" + sql);
        try(Connection conn = dataSource.getConnection()){
            PreparedStatement statement = conn.prepareStatement(sql);
            int i = statement.executeUpdate();
            return "执行成功，影响了" + i +"条数据";
        } catch (SQLException e) {
            logger.error("SQL执行失败",e);
            return "SQL执行失败：" + e;
        }
    }

    @Tool(description = "查询表的结构信息")
    public String describeTable(@ToolParam(description = "数据表名") String table) {
        logger.info("describeTable==>"+table);
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getColumns(null, null, table, null);

            List<Map<String, String>> columns = new ArrayList<>();
            while (rs.next()) {
                Map<String, String> column = new HashMap<>();
                column.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
                column.put("TYPE_NAME", rs.getString("TYPE_NAME"));
                column.put("COLUMN_SIZE", rs.getString("COLUMN_SIZE"));
                column.put("NULLABLE", rs.getString("IS_NULLABLE"));
                column.put("REMARKS", rs.getString("REMARKS"));
                column.put("COLUMN_DEF", rs.getString("COLUMN_DEF"));
                columns.add(column);
            }
            return objectMapper.writeValueAsString(columns);
        } catch (Exception e) {
            throw new RuntimeException("查询表结构错误: " + e.getMessage());
        }
    }

    @Tool(description = "在数据库中创建新表")
    public String createTable(@ToolParam(description = "建表语句") String query) {
        if (!query.strip().toUpperCase().startsWith("CREATE TABLE")) {
            throw new RuntimeException("Only CREATE TABLE statements are allowed", null);
        }
        logger.info("createTable==>"+query);
        return updateBySql(query);
    }

}
